﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Module
{
    public class SelectAllClass : DBInteractionBase
    {
        public DataTable ThongTinBenhVien()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_ThongTinBenhVien_SelectAll", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_ThongTinBenhVien_SelectAll: " + ex.Message);
            }
        }

        public DataTable SelectAllLoaiKhachHang()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_DMKhachHang_SelectAllLoaiKhachHang", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_DMKhachHang_SelectAllLoaiKhachHang: " + ex.Message);
            }
        }

        public DataTable SelectAllLoaiVatTu()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMNhomVatTu_SelectAllLoaiVatTu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMNhomVatTu_SelectAllLoaiVatTu: " + ex.Message);
            }
        }

        public DataTable SelectAllNhomVatTu(string maloaivattu)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMVatTu_SelectAllNhomVatTu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaLoaiVatTu", maloaivattu));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMVatTu_SelectAllNhomVatTu: " + ex.Message);
            }
        }

        public DataTable SelectAllVatTu(string manhomvatttu)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMVatTu_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaNhomVatTu", manhomvatttu));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMVatTu_Select: " + ex.Message);
            }
        }

        public DataTable SelectDonViById(string madonvi)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMDonVi_SelectById", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaDonVi", madonvi));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMDonVi_SelectById: " + ex.Message);
            }
        }

        public DataTable SelectAllDonVi()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMDonVi_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMDonVi_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllThue()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMThue_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMThue_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllKho()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMKho_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMKho_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllNhaCungCap()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_DMNhaCungCap_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_DMNhaCungCap_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllNguonGocXuatXu()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_DMNguonGocXuatXu_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_DMNguonGocXuatXu_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllKhoa()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMKhoa_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMKhoa_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllLoaiDichVu()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMNhomDichVu_SelectAllLoaiDichVu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMNhomDichVu_SelectAllLoaiDichVu: " + ex.Message);
            }
        }

        public DataTable SelectAllNhomDichVu()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMDichVu_SelectAllNhomDichVu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMDichVu_SelectAllNhomDichVu: " + ex.Message);
            }
        }

        public DataTable SelectAllDichVuXetNghiem()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMDichVuChiTiet_SelectAllXetNghiem", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMDichVuChiTiet_SelectAllXetNghiem: " + ex.Message);
            }
        }

        public DataTable SelectAllMaBenhICD()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMMaBenhICD_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMMaBenhICD_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllBuong()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMBuong_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMBuong_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllNhomDoiTuongBaoHiem()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMNhomDoiTuongBaoHiem_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMNhomDoiTuongBaoHiem_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllDoiTuongBaoHiem()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMDoiTuongBaoHiem_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMDoiTuongBaoHiem_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllTinhThanh()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMTinhThanh_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMTinhThanh_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllQuanHuyen()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMQuanHuyen_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMQuanHuyen_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllTaiKhoan()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMTaiKhoan_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMTaiKhoan_Select: " + ex.Message);
            }
        }

        public DataTable SelectAllChucVu()
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMChucVu_Select", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMChucVu_Select: " + ex.Message);
            }
        }

        public DataTable SelectKhachHangByMaLoaiKhachHang(string maloaikhachhang)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_DMKhachHang_SelectByMaLoaiKhachHang", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaLoaiKhachHang", maloaikhachhang));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_DMKhachHang_SelectByMaLoaiKhachHang: " + ex.Message);
            }
        }

        public DataTable SelectVatTuById(string mavattu)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMVatTu_SelectById", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaVatTu", mavattu));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMVatTu_SelectById: " + ex.Message);
            }
        }

        public DataTable SelectVatTuByMaKho(string makho)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_SelectVatTuByMaKho", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaKho", makho));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_SelectVatTuByMaKho: " + ex.Message);
            }
        }

        public DataTable SelectTaiKhoanById(string id)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMTaiKhoan_SelectById", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@TaiKhoan", id));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMTaiKhoan_SelectById: " + ex.Message);
            }
        }

        public DataTable SelectKhoByKhoa(string makhoa)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_SelectDMKhoByKhoa", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaKhoa", makhoa));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_SelectDMKhoByKhoa: " + ex.Message);
            }
        }

        public DataTable SelectPhongBanByKhoa(string makhoa)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMPhongBan_SelectByMaKhoa", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaKhoa", makhoa));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tb_DMPhongBan_SelectByMaKhoa: " + ex.Message);
            }
        }

        public DataTable CheckTon_Ma_Lo_PhieuNhap(string mavattu, string losanxuat, string maphieunhap, string makho)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_CheckTon_Ma_Lo_PhieuNhap", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaVatTu", mavattu));
                    dbcmd.Parameters.Add(new SqlParameter("@LoSanXuat", losanxuat));
                    dbcmd.Parameters.Add(new SqlParameter("@MaPhieuNhap", maphieunhap));
                    dbcmd.Parameters.Add(new SqlParameter("@MaKho", makho));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_CheckTon_Ma_Lo: " + ex.Message);
            }
        }

        public DataTable CheckTon_Ma_Lo_PhieuNhap_NotHSD(string mavattu, string losanxuat, string maphieunhap,
            string makho)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_CheckTon_Ma_Lo_PhieuNhap_NotHSD", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaVatTu", mavattu));
                    dbcmd.Parameters.Add(new SqlParameter("@LoSanXuat", losanxuat));
                    dbcmd.Parameters.Add(new SqlParameter("@MaPhieuNhap", maphieunhap));
                    dbcmd.Parameters.Add(new SqlParameter("@MaKho", makho));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_CheckTon_Ma_Lo_PhieuNhap_NotHSD: " + ex.Message);
            }
        }

        public DataTable CheckTon_MaKho_MaVatTu(string makho, string mavattu)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_CheckTon_MaKho_MaVatTu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaKho", makho));
                    dbcmd.Parameters.Add(new SqlParameter("@MaVatTu", mavattu));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_CheckTon_MaKho_MaVatTu: " + ex.Message);
            }
        }

        public DataTable CheckTonThucTe_MaKho_MaVatTu(string makho, string mavattu)
        {
            var dt = new DataSet();
            try
            {
                using (var dbcmd = new SqlCommand("tbDuoc_CheckTonThucTe_MaKho_MaVatTu", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@MaKho", makho));
                    dbcmd.Parameters.Add(new SqlParameter("@MaVatTu", mavattu));
                    var dtAdapter = new SqlDataAdapter(dbcmd);
                    dtAdapter.Fill(dt);
                    return dt.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception("tbDuoc_CheckTonThucTe_MaKho_MaVatTu: " + ex.Message);
            }
        }

        public bool TrangThaiTaiKhoan(string taikhoan, out bool status)
        {
            try
            {
                using (var dbcmd = new SqlCommand("tb_DMTaiKhoan_TrangThaiTaiKhoan", GetConnection()))
                {
                    dbcmd.CommandType = CommandType.StoredProcedure;
                    dbcmd.Parameters.Add(new SqlParameter("@TaiKhoan", taikhoan));
                    dbcmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.Bit)
                    {
                        Direction = ParameterDirection.Output
                    });
                    dbcmd.ExecuteNonQuery();
                    status = Convert.ToBoolean(dbcmd.Parameters["@Status"].Value);
                }
                return status;
            }
            catch (Exception)
            {
                status = false;
                return false;
            }
        }
    }
}